package game.module.club.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import game.common.DaoCommonHandler;
import game.db.DataSourceManager;
import game.module.club.bean.Club;
import game.module.club.bean.ClubApply;
import game.module.club.bean.ClubCodeBean;
import game.module.club.bean.ClubDonateDetail;
import game.module.club.bean.ClubInviteBean;
import game.module.club.bean.ClubMember;
import game.module.club.bean.ClubNameUsedBean;

/**
 * @author: Pomelo.Lee
 * @date: 2017年8月29日 上午11:17:38
 */
public class ClubDao {

	private static Logger logger = LoggerFactory.getLogger(ClubDao.class);

	private DataSource dataSource = DataSourceManager.getInstance().getDataSource();

	static class SingletonHolder {
		static ClubDao instance = new ClubDao();
	}

	public static ClubDao getInstance() {
		return SingletonHolder.instance;
	}

	private ResultSetHandler<ClubCodeBean> oneClubCodeHandler = new ResultSetHandler<ClubCodeBean>() {
		@Override
		public ClubCodeBean handle(ResultSet rs) throws SQLException {
			ClubCodeBean bean = null;
			if (rs.next()) {
				bean = packBeanFromRs(rs);
			}
			return bean;
		}
	};

	private ClubCodeBean packBeanFromRs(ResultSet rs) throws SQLException {
		ClubCodeBean bean = new ClubCodeBean();
		bean.setId(rs.getInt("id"));
		bean.setPlayerId(rs.getInt("player_id"));
		bean.setPhone(rs.getString("phone"));
		bean.setCode(rs.getString("code"));
		bean.setSendTime(rs.getTimestamp("send_time"));
		bean.setIsValid(rs.getInt("is_valid"));
		bean.setUseTime(rs.getTimestamp("use_time"));
		return bean;
	}

	public boolean addClubCode(ClubCodeBean bean) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update("insert into club_code(player_id,phone,code,send_time,is_valid) values(?,?,?,NOW(),0)", bean.getPlayerId(), bean.getPhone(), bean.getCode());
			if (ret > 0) {
				addRet = true;
			}
			// get id
			if (addRet) {
				int id = runner.query("SELECT LAST_INSERT_ID()", DaoCommonHandler.Integerhandler);
				bean.setId(id);
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	public ClubCodeBean getClubCodeByPlayerId(int playerId) {
		ClubCodeBean ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_code where player_id = ?  and is_valid=0  order by id desc", oneClubCodeHandler, playerId);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	public void updateClubCodeBean(ClubCodeBean bean) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			String updateSql = null;
			if (bean.getUseTime() != null) {
				updateSql = "update club_code set is_valid = ? ,use_time=?,phone=? where id=?";
				runner.update(updateSql, bean.getIsValid(), bean.getUseTime(), bean.getPhone(), bean.getId());
			} else {
				updateSql = "update club_code set is_valid = ? ,send_time=?,phone=? where id=?";
				runner.update(updateSql, bean.getIsValid(), bean.getSendTime(), bean.getPhone(), bean.getId());
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public boolean addClubNameUsed(String clubName) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update("insert into club_name_used(club_name,create_time) values(?,NOW())", clubName);
			if (ret > 0) {
				addRet = true;
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	// -----------------------club name used start------------------------------
	private ResultSetHandler<List<ClubNameUsedBean>> mutiClubNameHandler = new ResultSetHandler<List<ClubNameUsedBean>>() {

		@Override
		public List<ClubNameUsedBean> handle(ResultSet rs) throws SQLException {
			List<ClubNameUsedBean> beanList = new ArrayList<>();
			while (rs.next()) {
				ClubNameUsedBean bean = new ClubNameUsedBean();
				bean.setId(rs.getInt("id"));
				bean.setClubName(rs.getString("club_name"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<ClubNameUsedBean> listAllUsedClubName() {
		List<ClubNameUsedBean> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_name_used ", mutiClubNameHandler);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	// -----------------------club name used end------------------------------

	// -----------------------club data start---------------------------------
	private ResultSetHandler<List<Club>> mutiClubHandler = new ResultSetHandler<List<Club>>() {

		@Override
		public List<Club> handle(ResultSet rs) throws SQLException {
			List<Club> beanList = new ArrayList<>();
			while (rs.next()) {
				Club bean = new Club();
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setPhone(rs.getString("phone"));
				bean.setNum(rs.getInt("num"));
				bean.setFoundId(rs.getInt("found_id"));
				bean.setFoundName(rs.getString("found_name"));
				bean.setCdrId(rs.getInt("cdr_id"));
				bean.setCdrName(rs.getString("cdr_name"));
				bean.setCdrWeixin(rs.getString("cdr_weixin"));
				bean.setLiveNessNum(rs.getLong("liveness_num"));
				bean.setLevel(rs.getInt("level"));
				bean.setJinDou(rs.getLong("jin_dou"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				bean.setDleTime(rs.getTimestamp("del_time"));
				bean.setDelFlag(rs.getInt("del_flag"));
				String notice = rs.getString("notice");
				notice = notice == null ? "" : notice;
				bean.setNotice(notice);

				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<Club> listValidAllClub() {
		List<Club> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club where del_flag=0 ", mutiClubHandler);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	private ResultSetHandler<List<ClubMember>> mutiClubMemberHandler = new ResultSetHandler<List<ClubMember>>() {

		@Override
		public List<ClubMember> handle(ResultSet rs) throws SQLException {
			List<ClubMember> beanList = new ArrayList<>();
			while (rs.next()) {
				ClubMember bean = new ClubMember();
				bean.setId(rs.getInt("id"));
				bean.setClubId(rs.getInt("club_id"));
				bean.setPlayerId(rs.getInt("player_id"));
				bean.setPlayerName(rs.getString("player_name"));
				bean.setHeadimg(rs.getString("headimg"));
				bean.setAuth(rs.getInt("auth"));
				bean.setCredit_num(rs.getInt("credit_num"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<ClubMember> listMemberByClubId(int clubId) {
		List<ClubMember> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_member where club_id=? ", mutiClubMemberHandler, clubId);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	public void updateClubMemberBean(ClubMember clubMember) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("update club_member set credit_num=?,auth=?  where id=?", clubMember.getCredit_num(), clubMember.getAuth(), clubMember.getId());
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public void delClubMemberByPlayerId(int playerId) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("delete from club_member where player_id=?", playerId);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public void delClubMemberByClubId(int clubId) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("delete from club_member where club_id=?", clubId);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public boolean saveClub(Club club) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update(
					"insert into club(id,name,phone,num,found_id,found_name,cdr_id,cdr_name,cdr_weixin,liveness_num,level,jin_dou,create_time,del_flag) values(?,?,?,?,?,?,?,?,?,?,?,?,?,0)",
					club.getId(), club.getName(), club.getPhone(), club.getNum(), club.getFoundId(), club.getFoundName(), club.getCdrId(), club.getCdrName(), club.getCdrWeixin(),
					club.getLiveNessNum(), club.getLevel(), club.getJinDou(), club.getCreateTime());
			if (ret > 0) {
				addRet = true;
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	public void delClubBean(Club bean) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			String updateSql = null;
			updateSql = "update club set del_flag = 1 ,del_time=? where id=?";
			runner.update(updateSql, bean.getDleTime(), bean.getId());
		} catch (

		SQLException e) {
			logger.error("", e);
		}
	}

	public void updateClubBean(Club bean) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			String updateSql = null;
			updateSql = "update club set cdr_id = ? ,cdr_name=?,cdr_weixin=?,liveness_num=?,level=?,jin_dou=?,notice=? where id=?";
			runner.update(updateSql, bean.getCdrId(), bean.getCdrName(), bean.getCdrWeixin(), bean.getLiveNessNum(), bean.getLevel(), bean.getJinDou(), bean.getNotice(),
					bean.getId());
		} catch (

		SQLException e) {
			logger.error("", e);
		}
	}

	public boolean saveClubMember(ClubMember clubMember) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update("insert into club_member(club_id,player_id,player_name,headimg,auth,credit_num,create_time) values(?,?,?,?,?,?,?)", clubMember.getClubId(),
					clubMember.getPlayerId(), clubMember.getPlayerName(), clubMember.getHeadimg(), clubMember.getAuth(), clubMember.getCredit_num(), clubMember.getCreateTime());
			if (ret > 0) {
				addRet = true;
			}
			if (addRet) {
				int id = runner.query("SELECT LAST_INSERT_ID()", DaoCommonHandler.Integerhandler);
				clubMember.setId(id);
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	// -----------------------club data end-----------------------------------
	// ------------------------club apply start--------------------------------
	public boolean saveClubApply(ClubApply clubApply) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update("insert into club_apply(club_id,player_id,create_time) values(?,?,?)", clubApply.getClubId(), clubApply.getPlayerId(),
					clubApply.getCreateTime());
			if (ret > 0) {
				addRet = true;
			}
			if (addRet) {
				int id = runner.query("SELECT LAST_INSERT_ID()", DaoCommonHandler.Integerhandler);
				clubApply.setId(id);
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	private ResultSetHandler<List<ClubApply>> mutiClubApplyHandler = new ResultSetHandler<List<ClubApply>>() {

		@Override
		public List<ClubApply> handle(ResultSet rs) throws SQLException {
			List<ClubApply> beanList = new ArrayList<>();
			while (rs.next()) {
				ClubApply bean = new ClubApply();
				bean.setId(rs.getInt("id"));
				bean.setClubId(rs.getInt("club_id"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				bean.setPlayerId(rs.getInt("player_id"));
				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<ClubApply> listAllClubApply() {
		List<ClubApply> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_apply ", mutiClubApplyHandler);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	public void delClubApplyByPlayerId(int playerId) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("delete from club_apply where player_id=?", playerId);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public void delClubApplyByPlayerIdAndClubId(int playerId, int clubId) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("delete from club_apply where player_id=? and club_id=?", playerId, clubId);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	public void delClubApplyByClubId(int clubId) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			runner.update("delete from club_apply where club_id=?", clubId);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

	// ------------------------club apply end----------------------------------

	// -------------------------club donate detail start------------------------
	public boolean saveClubDonateDetail(ClubDonateDetail bean) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update(
					"insert into club_donate_detail(club_id,player_id,player_name,act,act_jin_dou,retain_jin_dou,create_time,act_player_id,act_player_name) values(?,?,?,?,?,?,?,?,?)",
					bean.getClubId(), bean.getPlayerId(), bean.getPlayerName(), bean.getAct(), bean.getActJinDou(), bean.getRetainJinDou(), bean.getCreateTime(),
					bean.getActPlayerId(), bean.getActPlayerName());
			if (ret > 0) {
				addRet = true;
			}
			if (addRet) {
				int id = runner.query("SELECT LAST_INSERT_ID()", DaoCommonHandler.Integerhandler);
				bean.setId(id);
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	private ResultSetHandler<List<ClubDonateDetail>> mutiDonateHandler = new ResultSetHandler<List<ClubDonateDetail>>() {

		@Override
		public List<ClubDonateDetail> handle(ResultSet rs) throws SQLException {
			List<ClubDonateDetail> beanList = new ArrayList<>();
			while (rs.next()) {
				ClubDonateDetail bean = new ClubDonateDetail();
				bean.setId(rs.getInt("id"));
				bean.setClubId(rs.getInt("club_id"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				bean.setPlayerId(rs.getInt("player_id"));
				bean.setAct(rs.getInt("act"));
				bean.setActJinDou(rs.getLong("act_jin_dou"));
				bean.setRetainJinDou(rs.getLong("retain_jin_dou"));
				bean.setPlayerName(rs.getString("player_name"));
				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<ClubDonateDetail> listDonateDetailByClubId(int clubId) {
		List<ClubDonateDetail> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_donate_detail where club_id=? order by id desc limit 500 ", mutiDonateHandler, clubId);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	// -------------------------club donate detail end--------------------------
	public boolean saveClubInviteBean(ClubInviteBean bean) {
		boolean addRet = false;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			int ret = runner.update("insert into club_invite(player_id,status,club_id,create_time) values(?,?,?,?)", bean.getPlayerId(), 0, bean.getClubId(), bean.getCreateTime());
			if (ret > 0) {
				addRet = true;
			}
			if (addRet) {
				int id = runner.query("SELECT LAST_INSERT_ID()", DaoCommonHandler.Integerhandler);
				bean.setId(id);
			}
		} catch (SQLException e) {
			logger.error("", e);
		}
		return addRet;
	}

	private ResultSetHandler<List<ClubInviteBean>> mutiInviteHandler = new ResultSetHandler<List<ClubInviteBean>>() {
		@Override
		public List<ClubInviteBean> handle(ResultSet rs) throws SQLException {
			List<ClubInviteBean> beanList = new ArrayList<>();
			while (rs.next()) {
				ClubInviteBean bean = new ClubInviteBean();
				bean.setId(rs.getInt("id"));
				bean.setCreateTime(rs.getTimestamp("create_time"));
				bean.setPlayerId(rs.getInt("player_id"));
				bean.setStatus(rs.getInt("status"));
				bean.setClubId(rs.getInt("club_id"));
				beanList.add(bean);
			}
			return beanList;
		}
	};

	public List<ClubInviteBean> listClubInviteBeanAll() {
		List<ClubInviteBean> ret = null;
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			ret = runner.query("SELECT * from club_invite  ", mutiInviteHandler);
		} catch (SQLException e) {
			logger.error("", e);
			return ret;
		}
		return ret;
	}

	public void updateClubInvite(int id, int status) {
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			// 0.邀请中 1.已同意 2.已经拒绝
			String updateSql = "update club_invite set status = ?  where id=?";
			runner.update(updateSql, status, id);
		} catch (SQLException e) {
			logger.error("", e);
		}
	}

}
